﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace VideoMonitorSystem.DAL
{
    /// <summary>
    /// 数据库连接类
    /// </summary>
    public class DBHelper
    {
        static SqlConnection connection;
      
        //初始化数据库连接
        static DBHelper()
        {
            if (connection == null)
            {
                connection = new SqlConnection( ConfigurationManager.ConnectionStrings["SQL"].ConnectionString);
            }else if(connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }else if(connection.State == ConnectionState.Broken)
            {
                connection.Close();
                connection.Open();
            }

        }
        /// <summary>
        /// 带参数的基础查询
        /// </summary>
        /// <param name="sql">SQL命令语句</param>
        /// <param name="par">SQL语句中需要的参数值</param>
        /// <returns>查询出的结果集</returns>
        public static DataTable GetDataTable(string sql,params SqlParameter[] par)
        {
            DataSet dataSet = new DataSet();
            SqlCommand cmd = new SqlCommand(sql, connection);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
            sqlDataAdapter.Fill(dataSet);
            connection.Close();
            return dataSet.Tables[0];
        }
        /// <summary>
        /// 无参查询
        /// </summary>
        /// <param name="sql">SQL命令语句</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql)
        {
            return GetDataTable(sql, new SqlParameter[0]);
        }
        /// <summary>
        /// 带参数 获取SQL语句执行后影响的行数
        /// </summary>
        /// <param name="sql">SQL命令语句</param>
        /// <param name="values">SQL语句中需要的参数值</param>
        /// <returns>执行后影响的行数</returns>
        public static int GetScalar(string sql,params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, connection);
            cmd.Parameters.AddRange(values);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            connection.Close();
            return result;
        }
        /// <summary>
        /// 无参  获取SQL语句执行后影响的行数
        /// </summary>
        /// <param name="sql">SQL命令语句</param>
        /// <returns>执行后影响的行数</returns>
        public static int GetScalar(string sql)
        {
            return GetScalar(sql,new SqlParameter[0]);
        }

    }

     
}
